#!/usr/bin/python
# -*- coding: utf-8 -*-
# author: Miguel Nieto

import sqlite3, os
from bottle import route, run, debug, template, request, error, send_file


'''
    machine list
'''
@route('/machinelist')
def machine_list():
    conn = sqlite3.connect('machines.db')
    c = conn.cursor()
    c.execute("SELECT name FROM machines ORDER BY name ASC")
    nam = c.fetchall() # machine list
    c.execute("SELECT tag FROM tags ORDER BY tag ASC")
    tag = c.fetchall() # tag list
    c.close()
    names=list()
    tags=list()
    '''extraction of machine names and associated tags'''
    for n in nam:
        na = n[0].encode('utf-8')
        if na not in names:
            names.append(na)
    for t in tag:
        ta = t[0].encode('utf-8')
        if ta not in tags:
            tags.append(ta)
    
    return template('machine_list.tpl', names=names, tags=tags)

'''
    shows a machine whose name is specified
'''
@route('/show/:na')
@route('/show/:na/')#in case it's wrongly typed
                    #(some parts of the page will try to access a machine in this way if it has no version)
def show_item_name(na):
    conn = sqlite3.connect('machines.db')
    c = conn.cursor()
    
    '''individual extraction of data from the database.
    Ordered by id so that the fields match in one machine'''
    query="SELECT id FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query)
    id = c.fetchall() 
    query="SELECT name FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query)
    nam = c.fetchall()
    query="SELECT author FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query)
    auth = c.fetchall()
    query="SELECT description FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query)
    desc = c.fetchall()
    query="SELECT version FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query) 
    vers = c.fetchall()
    query="SELECT dependencies FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query) 
    depend = c.fetchall()
    query="SELECT nature FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query) 
    nature = c.fetchall()
    query="SELECT application FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query) 
    applic = c.fetchall()
    c.close()    
    
    ids = list()
    for i in id:
        ids.append(i)
    ''' a dictionary is created containing other dictionary, or others,
        in case there are several machines with the same name, one
        dictionary per machine containing its full information.'''
    files = dict()
    if ids: #if there is any element
        for j in range(len(ids)):
            file = dict()
            file['id']=ids[j][0]
            file['name'] = nam[j][0].encode('utf-8')
            file['author'] = auth[j][0].encode('utf-8')
            file['description'] = desc[j][0].encode('utf-8')
            
            '''extracted tags from the corresponding entries from the 'tags' table'''
            tags = list()
            c.execute("SELECT tag FROM tags WHERE idtag LIKE %s ORDER BY tag ASC" %file['id'] )
            tlist = c.fetchall() # tag list
            for t in tlist:
                ta = t[0].encode('utf-8')
                if ta not in tags:
                    tags.append(ta)
                
            file['tags'] = tags
            file['version'] = vers[j][0].encode('utf-8')
            file['dependencies'] = depend[j][0].encode('utf-8')
            file['nature'] = nature[j][0].encode('utf-8')
            file['application'] = applic[j][0].encode('utf-8')
            files[j] = file
    
        return template('machine.tpl', files=files, mach=na)
    else:
        return template('unknown_machine.tpl', unk=na) #in case the machine does not exist

'''
    shows a machine whose name and version are specified
'''
@route('/show/:na/:ve')
def show_item_name_n_version(na,ve):
    conn = sqlite3.connect('machines.db')
    c = conn.cursor()
    
    '''individual extraction of data from the database
    '''
    query="SELECT id FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query)
    id = c.fetchall() 
    query="SELECT name FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query)
    nam = c.fetchall()
    query="SELECT author FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query)
    auth = c.fetchall()
    query="SELECT description FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query)
    desc = c.fetchall()
    query="SELECT version FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query) 
    vers = c.fetchall()
    query="SELECT dependencies FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query) 
    depend = c.fetchall()
    query="SELECT nature FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query) 
    nature = c.fetchall()
    query="SELECT application FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query) 
    applic = c.fetchall()
    c.close()    
    
    ids = list()
    for i in id:
        ids.append(i)
    ''' a dictionary is created containing other dictionary, or others,
        in case there are several machines with the same name, one
        dictionary per machine.
    '''
    files = dict()
    if ids: #if there is any element
        for j in range(len(ids)):
            file = dict()
            file['id']=ids[j][0]
            file['name'] = nam[j][0].encode('utf-8')
            file['author'] = auth[j][0].encode('utf-8')
            file['description'] = desc[j][0].encode('utf-8')
            
            '''extracted tags from the corresponding entries from the 'tags' table'''
            tags = list()
            c.execute("SELECT tag FROM tags WHERE idtag LIKE %s ORDER BY tag ASC" %file['id'] )
            tlist = c.fetchall() # tag list
            for t in tlist:
                ta = t[0].encode('utf-8')
                if ta not in tags:
                    tags.append(ta)
                
            file['tags'] = tags
            file['version'] = vers[j][0].encode('utf-8')
            file['dependencies'] = depend[j][0].encode('utf-8')
            file['nature'] = nature[j][0].encode('utf-8')
            file['application'] = applic[j][0].encode('utf-8')
            files[j] = file
    
        return template('machine.tpl', files=files, mach=na)
    else:
        return template('unknown_machine.tpl', unk=na, version=ve) # in case the machine does not exist

'''
    machine upload page
'''
@route('/upload')
def upload():
    return template('upload_machine.tpl')

'''
    machine upload checking and showing page
'''
@route('/uploadcheck', method='POST')
def upload_check():    
    datafile = request.POST['echfile']
    try: #Internet Explorer includes the full path in the file name, so it's extracted in case it appears
        filename = datafile.filename.rpartition('\\')[-1]
    except AttributeError:
        return template('upload_unknown_file.tpl')    
    try: #confirmation for inserting the processed file into the database is extracted
        process = request.POST['process']
    except KeyError:
        process = ''    
    sfilename = filename.rpartition('.') # file name separated from extension

    '''file content extraction process adapted to the collected information characteristics by POST method'''
    if sfilename[2] == 'ech':
        datastr = datafile.value # file content
        
        try: #information of the file package belonging is extracted
            path = datastr.split('package ')[-1].split(';', 1)[0].replace('.', '/') + '/'
        except IndexError:
            path = ''
        try: #creates the path for the file to be saved on it at a folder in this project
            os.makedirs('../files/' + path)
        except:
            pass
        '''saving the file content in the created new file'''
        tempfile = open('../files/' + path + filename, 'w')
        tempfile.write(datastr)
        tempfile.close()
        '''creating the diagram description file (dot). Terminal command'''
        executor = 'python ../bin/ech2dot --target-directory ../files --echartspath ../files/ '
        executor += '../files/' + path + filename        
        os.system(executor)
        '''if a problem is produced, its error message is saved in this file'''
        ferr = open('../files/machineNotFound.log', 'r')
        err = ferr.read()
        ferr.close()
        if process: #if the user wants all the information to be processed and the diagram to be generated:
            conn = sqlite3.connect('machines.db')
            c = conn.cursor()
            '''file information extraction. It deletes carriage returns and leading and trailing spaces'''
            try:
                name = datastr.split('@name ')[1].split('\n', 1)[0].strip(' ')
                if name != sfilename[0]:
                    raise IndexError
            except IndexError:
                return template('upload_machine_error.tpl')
            try:
                author = datastr.split('@author ')[1].split('\n',1)[0].strip(' ')
            except IndexError:
                author = ''
    
            '''consecutive substitutions of strings in description in order to eliminate Java comment asterisks'''
    
            description = datastr.split('@description ')[1].split('@description')[0]
            description = description.replace('\n','<br/>').replace('<br/> *', '<br/>').strip(' ')
            '''elimination of unnecessary spaces after line ends.
            the loop will never be infinite because in case it finds the string,
            it deletes it so it will disappear'''
            while description.find('<br/> ') != -1:
                description = description.replace('<br/> ', '<br/>')
    
            try:
                tags = datastr.split('@tags ')[1].split('\n', 1)[0].strip(' ')
            except IndexError:
                tags = ''
            try:
                version = datastr.split('@version ')[1].split('\n', 1)[0].strip(' ')
            except IndexError:
                version = ''
            try:
                dependencies = datastr.split('@dependencies ')[1].split('\n', 1)[0].strip(' ')
            except IndexError:
                dependencies = ''
            try:
                nature = datastr.split('@nature ')[1].split('\n', 1)[0].strip(' ')
            except IndexError:
                nature = ''
            try:
                application = datastr.split('@application ')[1].split('\n', 1)[0].strip(' ')
            except IndexError:
                application = ''
            '''insertion of every collected data into the 'machines' database table'''
            query = '''INSERT INTO machines (name,author,description,tags,version,dependencies,nature,application) 
                    VALUES ('%s','%s','%s','%s','%s','%s','%s','%s')''' % (name, author, description, tags,
                                                                           version, dependencies, nature, application)

            c.execute(query)
            conn.commit()
            '''once the machine is inserted in the 'machines' table
            their tags will be consistently inserted in the 'tags' table'''
            c.execute("SELECT last_insert_rowid()")
            new_id = c.fetchone()[0]
            '''extraction of tags from 'tags' field and insertion into the 'tags' database table'''
            query="SELECT tags FROM machines WHERE id LIKE '%s'" %new_id
            c.execute(query)
            tags = c.fetchone()[0].encode('utf-8')
            tlist = tags.split(';')
            for t in tlist:
                query="INSERT INTO tags (idtag, tag) VALUES (%s, '%s')" %(new_id,t.strip(' ')) 
                try:
                    c.execute(query)
                except sqlite3.IntegrityError:
                    pass
                conn.commit()
            c.close()
            '''generation of flow charts. Terminal command'''
            executor = 'dot -Tpng -Efontsize=12 -Elabelfontsize=8 -Gfontsize=12 -Gsize="10.5,7" -o ../images/'
            executor += sfilename[0] + version + '.png ../files/' + path + sfilename[0] + '.dot'
            os.system(executor)
                
            return template('check_upload.tpl', name=name, author=author, version=version,
                            description=description, dependencies=dependencies, nature=nature,
                            tlist=tlist, application=application, id=new_id, err=err)  
        else:
            return template('not_processed_upload.tpl', filename=filename, err=err)
    else:
        return template('wrong_file.tpl', filename=filename)
    
'''
    tag search
'''
@route('/showtag/:ta')
def find_tag(ta):
    conn = sqlite3.connect('machines.db')
    c = conn.cursor()
    query = "SELECT idtag FROM tags WHERE tag like '%s'" %ta 
    c.execute(query)
    ids = c.fetchall()
    mach = dict()
    for id in ids:
        query = "SELECT name FROM machines WHERE id LIKE %s" %id[0]
        c.execute(query)
        name = c.fetchone()[0].encode('utf-8')
        query = "SELECT version FROM machines WHERE id LIKE %s" %id[0]
        c.execute(query)
        version = c.fetchone()[0].encode('utf-8')
        mach[name] = version
    
    c.close()
    return template('show_tag.tpl', machines=mach, tag=ta)

'''
    machine deletion
'''
@route('/delmachine/:id')
def delete_machine(id):
    conn = sqlite3.connect('machines.db')
    c = conn.cursor()
    query = "DELETE FROM machines WHERE id LIKE %s" %id
    c.execute(query)
    conn.commit()
    query = "DELETE FROM tags WHERE idtag LIKE %s" %id
    c.execute(query)
    conn.commit()
    c.close()
    return template('deleted_machine.tpl', id=id)

'''
    image showing
'''
@route('/image/:ima#.*\.png#')
def static_image(ima):
    send_file(ima, root='../images', mimetype='image/png')


@error(403)
def mistake403(code):
    return 'The passed parameter has the incorrect format'
@error(404)
def mistake404(code):
    return 'Sorry, the page could not be found'

debug(False) #True for debugging
run(reloader=False) #True for detecting changes in code so the server is automatically restarted